<?
//include all support file
include_once ("../modconfig.php");
include_once ("$ROOT_CLASS_DIR/class_database.php");
include_once ("$ROOT_CLASS_DIR/class_common.php");
include_once ("$ROOT_CLASS_DIR/class_login.php");

require_once('OLEwriter.php');
require_once('BIFFwriter.php');
require_once('Worksheet.php');
require_once('Workbook.php');
$pname = GetParam("pname","");
$cname = GetParam("cname","");
global $DBCoins,$DBGeneral,$DBConnection, $RS,$Index, $a, $b,$_CYear,$_CMonth,$_SMonth, $_LastMonth, $LMonth, $_LSMonth, $pname,$cname;


function HeaderingExcel($filename){
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:attachment;filename=$filename");
    header("Expires:0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Pragma: public");
}

//list($day,$month,$year)=explode("/", GetParam("date",""));

//$date = "$year-$month-$day";

//$namafile = $date."-OUT-".GetParam("prcode","");
$namafile = "Report_Project_perCustomer";

//http headers
HeaderingExcel($namafile);

//make a workbook
$workbook=new Workbook("-");

//make first worksheet
$fJudul=& $workbook->add_format();
$fJudul->set_bold();
$fJudul->set_size(14);

$fJudulKolom=& $workbook->add_format();
$fJudulKolom->set_bold();
$fJudulKolom->set_size(8);
$fJudulKolom->set_align('centre');
$fJudulKolom->set_border(1);

$fBesar=& $workbook->add_format();
$fBesar->set_size(20);

$fKutipan=& $workbook->add_format();
$fKutipan->set_align('right');
$fKutipan->set_italic();
$fKutipan->set_size(8);
$fKutipan->set_color('blue');

$fList=& $workbook->add_format();
$fList->set_size(8);

$fIsi=& $workbook->add_format();
$fIsi->set_border(1);

$fIsiTengah=& $workbook->add_format();
$fIsiTengah->set_border(1);
$fIsiTengah->set_align('centre');


$angka=& $workbook->add_format();
$angka->set_num_format('#,###');
$angka->set_border(1);

$angka2=& $workbook->add_format();
$angka2->set_num_format('#,###.00');
$angka2->set_border(1);

$angkatengah=& $workbook->add_format();
$angkatengah->set_num_format('#,###');
$angkatengah->set_border(1);
$angkatengah->set_align('centre');

$tanggal=& $workbook->add_format();
$tanggal->set_num_format('D-MMM-YYYY');
$tanggal->set_border(1);

$worksheet1= & $workbook->add_worksheet("Report");
//seting zoom size
//75 is 75% zoom size
$worksheet1->set_zoom(100);

//set portrait page
$worksheet1->set_portrait(100);

//set a4 paper size
$worksheet1->set_paper(9);

//set hide gridlines
$worksheet1->hide_gridlines();

//set print area
$worksheet1->print_area(0,0,15,5);

//set page header
$worksheet1->set_header("header",$margin=2);


//set column
$worksheet1->set_column(1,1,5);
$worksheet1->set_column(2,2,35);
$worksheet1->set_column(3,3,25);
$worksheet1->set_column(4,4,15);
$worksheet1->set_column(5,5,15);
$worksheet1->set_column(6,6,15);
$worksheet1->set_column(7,7,15);

//
$Month = GetParam("m","");
	$Year = GetParam("y","");
	if ($Month<10) $Month = "0".$Month;   
    		if (($Month-1)<10) {$LMonth="0".($Month-1);}
    		else $LMonth=$Month-1;
    	$_CYear  = $Year;
    	$_CMonth = $Month;
    	$_SMonth = "aku".$Month;
    	$_LastMonth = "aku".$LMonth;
    	if ($Month==1) $_LSMonth = "awal";    
    	else $_LSMonth = "aku".$LMonth;    

// buat koneksi database dan cari data
$DBConnection   = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

//HEADER

$title = "REPORT PROJECT perCUSTOMER";
$page   = 'HAL';
$title .= (GetParam("m","")=="13")?' (AUDIT)':"";
$perush = (GetParam("office",""))?GetCompany (GetParam("office","")):'PT. MASAJI PRAYASA CARGO';

//Header
$worksheet1->write_string(1,1,$title,$fJudul);
$worksheet1->write_string(2,1,$perush,$fJudul);
$today = date("d - F - Y");
$worksheet1->write_string(3,1,"PROJECT NAME : ".$pname,$fJudul);
$worksheet1->write_string(4,1,"CUSTOMER NAME : ".$cname,$fJudul);
$worksheet1->write_string(6,1,"NO",$fJudulKolom);
$worksheet1->write_string(6,2,"CUSTOMER NAME",$fJudulKolom);
$worksheet1->write_string(6,3,"NO JO",$fJudulKolom);
$worksheet1->write_string(6,4,"CURRENCY",$fJudulKolom);
$worksheet1->write_string(6,5,"REVENUE",$fJudulKolom);
$worksheet1->write_string(6,6,"COST",$fJudulKolom);
$worksheet1->write_string(6,7,"PROFIT",$fJudulKolom);

$SQL = "
		SELECT 
		 JOBORDER.jo_id,
		 JOBORDER.project_id,
		 PROJECT.project_name,
		 JOBORDER.debtur,
		 DEBTUR.CUNAME,
		 JOBORDER.jo_code,
		 JOBORDER.rat_idr as revenue,
		 JOCOST.total_amount,
		 sum(JOCOST.total_amount) as total_cost,
		 JOCOST.curr as currency
		FROM tbljoborder JOBORDER
		LEFT JOIN tblproject PROJECT
		 ON JOBORDER.project_id = PROJECT.project_id
		LEFT JOIN tbldebtur DEBTUR
		 ON JOBORDER.debtur = DEBTUR.CUCODE
		LEFT JOIN tbljoborder_costing JOCOST
		 ON JOBORDER.jo_id = JOCOST.jo_id   
          ";

$SQL .= " WHERE PROJECT.project_name like '%".$pname."%' AND DEBTUR.CUNAME like '%".$cname."%' ";
$SQL .= " GROUP BY JOBORDER.jo_code";
$SQL .= " ORDER BY DEBTUR.CUNAME ASC ";
//print_r($SQL);
$MyResults = $DBConnection->dbc->get_results($SQL,ARRAY_A);

$a=0;
if ($MyResults) {
//die('dalam else $SQL='.$SQL);
    foreach ($MyResults as $row){
	$a=$a+1;

	$worksheet1->write_string($a+6,1,$a,$fIsi);
	$worksheet1->write_string($a+6,2,$row["CUNAME"],$fIsi);
	$worksheet1->write_string($a+6,3,$row["jo_code"],$fIsi);
	$worksheet1->write_string($a+6,4,$row["currency"],$fIsi);
	$worksheet1->write_number($a+6,5,$row["revenue"],$fIsi);
	$worksheet1->write_number($a+6,6,$row["total_cost"],$fIsi);
	$totalAmount = $row["revenue"] - $row["total_cost"];
	$worksheet1->write_number($a+6,7,$totalAmount,$fIsi);
	
} //foreach
    
} // if 

function BuildPeriod ($year, $month) {
    $arraymonth = array("","JANUARI","FEBRUARI","MARET","APRIL","MEI","JUNI","JULI","AGUSTUS","SEPTEMBER","OKTOBER","NOPEMBER","DESEMBER");
    return strtoupper(date("d F Y", mktime(0, 0, 0, $month+1, 0, $year)));
  }


$workbook->close();
?>
